﻿IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('bd_proc_saveRepair')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE bd_proc_saveRepair

GO

-- =============================================
-- Author:      YFC
-- Create date: 2020-07-12 10:09:50
-- Database:    scm_main_hysj20200702
-- Description: 补出勤管理-保存补出勤
-- =============================================

CREATE PROCEDURE bd_proc_saveRepair
(
  @orderNo VARCHAR(50)
 ,@orderId BIGINT
 ,@userSerial BIGINT
 ,@pcIp VARCHAR(20)
 ,@repairStartDate DATE
 ,@repairEndDate DATE
 ,@startTime  INT
 ,@endTime  INT
 ,@userIds VARCHAR(MAX)
 ,@approver1  BIGINT
 ,@approver2  BIGINT
 ,@applyType  INT
 ,@remark VARCHAR(MAX)
 ,@repairType  INT
)
AS
BEGIN

  DECLARE @users TABLE
  (
    user_serial BIGINT
  )

  INSERT INTO @users(user_serial)
  SELECT value
  FROM STRING_SPLIT(@userIds,',') 


  IF NOT EXISTS (
      SELECT
        1
      FROM @users
    )
  BEGIN
     RAISERROR ('明细数据不能为空', 16, 1);
     RETURN 
  END


  CREATE TABLE #return_table
  (
    user_serial BIGINT NOT NULL,
    user_no VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    user_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    dept_name VARCHAR(64) COLLATE Chinese_PRC_CI_AS,
    error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
  )

  --生成日期
  DECLARE @date TABLE
  (
  	date_day DATETIME
  )
  
  DECLARE @dateFrom DATETIME = @repairStartDate
  DECLARE @dateTo DATETIME = @repairEndDate
  ;WITH tmpDays AS              
   (              
    SELECT @dateFrom date_value            
    UNION ALL             
    SELECT date_value + 1             
    FROM    tmpDays            
    WHERE   date_value + 1 <= @dateTo             
   )
  INSERT INTO @date (date_day)
  SELECT CONVERT(VARCHAR(10), date_value, 120) FROM tmpDays OPTION (MAXRECURSION 0);
  

  --日期和时间合并
  DECLARE @dateAll TABLE
  (
  	repair_date DATETIME
  )
  
  INSERT INTO @dateAll (repair_date)
  SELECT DATEADD(MINUTE, @startTime, date_day) FROM @date

  --开始时间和结束时间不添加
  IF @startTime != @endTime 
  BEGIN  
    INSERT INTO @dateAll (repair_date)
    SELECT DATEADD(MINUTE, @endTime, date_day) FROM @date
  END
  


  --考勤封账检测
  --验证要用的数据
  DECLARE @checkData TABLE
  (
 	  user_serial BIGINT,
    repair_date DATE,
    settle_year INT,
    settle_month  INT,
    settle_date DATE,
    is_lock INT DEFAULT 0,
    limit INT DEFAULT 0,
    is_save_time INT DEFAULT 0
  )
  
  INSERT INTO @checkData (user_serial, repair_date)
  SELECT user_serial, date_day FROM @users, @date

  --结算年月
  UPDATE @checkData SET settle_date = dbo.bd_fn_getSettleDate(repair_date)
  UPDATE @checkData SET settle_year = YEAR(settle_date), settle_month = MONTH(settle_date)

  UPDATE cd
  SET is_lock = 1
  FROM @checkData cd
  INNER JOIN bd_kq_accounts ac ON ac.user_serial = cd.user_serial AND ac.lock_status > 0 AND ac.year = cd.settle_year AND ac.month = cd.settle_month


  INSERT INTO #return_table(user_serial, error_msg)
  SELECT user_serial, '已封账的数据不能再修改'
  FROM @checkData
  WHERE ISNULL(is_lock, 0) > 0
  
  --删除有问题的数据
  DELETE FROM @checkData WHERE is_lock > 0
  DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)


  --每个人申请上限检测 
  DECLARE @limit INT = 3
  SELECT TOP 1 @limit = repair_limit FROM bd_sys_global_params 
  

  UPDATE cd
  SET limit = g.repair_count
  FROM @checkData cd
  INNER JOIN (
      SELECT
        x.user_serial, x.settle_year, x.settle_month, COUNT(1) repair_count
      FROM (
        SELECT DISTINCT cd.user_serial, CONVERT(VARCHAR(10), lj.sj, 120) repair_date, cd.settle_year, cd.settle_month
        FROM @checkData cd
        INNER JOIN lr_jl lj ON lj.user_serial = cd.user_serial AND lj.settle_year = cd.settle_year AND lj.settle_month = cd.settle_month
        INNER JOIN bd_repair_attendance main ON main.order_id = lj.order_id
        WHERE main.status >= 3
      ) x
      GROUP BY x.user_serial, x.settle_year, x.settle_month
    ) g ON g.user_serial = cd.user_serial

  
  INSERT INTO #return_table(user_serial, error_msg)
  SELECT user_serial, CAST(settle_year AS VARCHAR(4)) + '-' + CAST(settle_month AS VARCHAR(2)) + '补出勤申请已经超过上限'
  FROM @checkData
  WHERE ISNULL(limit, 0) >= @limit

  --删除有问题的数据
  DELETE FROM @checkData WHERE ISNULL(limit, 0) >= @limit
  DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)


  --相同时间验证
  INSERT INTO #return_table(user_serial, error_msg)
  SELECT lj.user_serial, CONVERT(VARCHAR(20), repair_date, 120) + '申请时间重复'
  FROM (
    SELECT
      repair_date, user_serial
    FROM @dateAll
        ,@users
  ) repair
  INNER JOIN lr_jl lj ON lj.user_serial = repair.user_serial AND lj.sj = repair.repair_date
  INNER JOIN bd_repair_attendance main ON lj.order_id = main.order_id AND ISNULL(main.is_del, 0) = 0

  DELETE FROM @users WHERE user_serial IN (SELECT user_serial FROM #return_table)


  --保存数据
  DECLARE @data TABLE
  (
  	user_serial BIGINT,
    repair_date DATETIME,
    settle_year INT,
    settle_month  INT,
    settle_date DATE
  )
  
  INSERT INTO  @data (user_serial, repair_date, settle_date)
  SELECT user_serial, repair_date, dbo.bd_fn_getSettleDate(repair_date)
  FROM @users, @dateAll

  UPDATE @data SET settle_year = YEAR(settle_date), settle_month = MONTH(settle_date)
  
  IF EXISTS(
  	SELECT 1
  	FROM @data
  ) 
  BEGIN
    DECLARE @total  INT
    SELECT @total = COUNT(1) FROM @users
  
    BEGIN TRY
      BEGIN TRAN
  
        INSERT INTO bd_repair_attendance (order_id, order_no, start_date, end_date, people_num, remark, creater_id, create_time, is_del, pc_ip, 
          status, approver1, approver2, apply_type, start_time, end_time, repair_type)
        VALUES (@orderId, @orderNo, @repairStartDate, @repairEndDate, @total, @remark, @userSerial, GETDATE(), 0, @pcIp, 
          0, @approver1, @approver2, @applyType, @startTime, @endTime, @repairType);
        
        INSERT INTO lr_jl (sj, user_serial, bz, fx, gly_no, img_path, whether_validity, order_id, pc_ip, settle_year, settle_month)
        SELECT repair_date, user_serial, NULL, 0, @userSerial, '', 0, @orderId, @pcIp, settle_year, settle_month
        FROM @data
  
  
      COMMIT TRAN
    END TRY
    BEGIN CATCH
      DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
  
      IF XACT_STATE() <> 0
      BEGIN
        ROLLBACK TRANSACTION;
      END;
      RAISERROR (@errStr, 16, 1);
    END CATCH  	
  END
  ELSE
  BEGIN
    IF NOT EXISTS(
    	SELECT 1
    	FROM #return_table
    ) 
    BEGIN
      INSERT INTO #return_table(user_serial, error_msg)
      SELECT 0, '没有明细数据，不能保存'    	
    END
  END


  UPDATE rt
  SET user_no = u.user_no
     ,user_name = u.user_lname
     ,dept_name = u.dep_name
  FROM #return_table rt
  INNER JOIN (
      SELECT
        user_serial, user_no, user_lname, dep_name
      FROM bd_view_user_all
    ) u ON u.user_serial = rt.user_serial

  SELECT user_serial, user_no, user_name, dept_name, error_msg FROM #return_table;

  DROP TABLE #return_table;

END
GO
